-- @owner: xue-xinyi610
-- @date: 2025/2/20
-- @testpoint: I8XDZ2 新增功能，创建100个触发器，依次禁用，最后插入数据。，触发器均未执行。。

-- 步骤pre1 清理旧表‌
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (id1 INT, id2 INT);

-- 步骤pre2 创建日志表‌
CREATE TABLE trigger_log (
  trigger_name TEXT,
  exec_time TIMESTAMP
);

-- 步骤pre3 创建通用触发器函数‌
CREATE OR REPLACE FUNCTION trigger_handler() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO trigger_log VALUES (TG_NAME, NOW());
  RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;/

-- 步骤pre4 批量创建100个触发器‌
BEGIN
  FOR i IN 1..100 LOOP
    EXECUTE format('CREATE TRIGGER tg_%s BEFORE INSERT ON test_table EXECUTE PROCEDURE trigger_handler()', i);
  END LOOP;
END;
/
-- 步骤pre5 禁用所有触发器‌
ALTER TABLE test_table ENABLE TRIGGER ALL;

-- 步骤test1 逐个禁用触发器‌
BEGIN
  FOR i IN 1..100 LOOP
    EXECUTE format('ALTER TABLE test_table DISABLE TRIGGER tg_%s', i);
  END LOOP;
END;/

-- 步骤test2 触发测试
INSERT INTO test_table VALUES (1,0);
INSERT INTO test_table VALUES (1,0);
select * from test_table;

-- 验证结果
SELECT count(*) FROM trigger_log; -- 预期返回0

-- 步骤post 清理对象‌
BEGIN
  FOR i IN 1..100 LOOP
    EXECUTE format('DROP TRIGGER tg_%s ON test_table', i);
  END LOOP;
END ;/
DROP FUNCTION trigger_handler;
DROP TABLE test_table, trigger_log;
